# MES
import socket
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler
from log import AppLogger
from spcparser import parse_scp,ngRows,okRows,statistics
from config import ConfigManager
import os
import datetime
import gv
import sqlite3
import re




class MyHandler(FileSystemEventHandler):
    def __init__(self,q) -> None:
        super().__init__()
        self.q=q
        
    def empty_db(self,file_info,errornum):
        # 删除旧的 sample.db 并建立新库
        # 获取当前程序运行目录
        if not os.path.exists('./db'):
            os.mkdir('./db')
        
        programeName = gv.sanitize_name(file_info['ProgramName']+"_"+file_info['BoardID'])
        dbname = './db/%s.db'%programeName
        if os.path.exists(dbname):
            os.remove(dbname)
            
        conn = sqlite3.connect(dbname)
        c = conn.cursor()
        c.execute("create table programname (id integer primary key,programname text,errornum integer)")
        c.execute("insert into programname (programname,errornum) values (?,?)",(programeName,errornum))
        c.execute("create table sample (BlockNo_InspectType text,ng integer)")
        # c.execute("create table detail (key text,errornum integer)")
        conn.commit()
        c.close()
        conn.close()
        return dbname
    
    def compare_sample(self,spc_fullname):
        log =  AppLogger('my_app.log', max_bytes=1024*20, backup_count=2)
        try:
            spc_fullname = os.path.normpath(spc_fullname)
            config = ConfigManager()
            paths = config.paths()
            self.q.put("clear")
            self.q.put("开始处理："+spc_fullname)
            
            # 解析文件
            file_info,c = parse_scp(spc_fullname,cpfile=False)
            
            newname = gv.sanitize_name(file_info['ProgramName']+"_"+file_info['BoardID'])
            if gv.sample_name!=newname:
                msg=f"compare_fail:新文件的程序名和条码与指定的不一样：{newname}：{gv.sample_name}"
                self.sendUdpMsg(msg)
                return
            
            c.execute("create table sample (BlockNo_InspectType text,ng integer)")
            c.executescript("""INSERT INTO sample (BlockNo_InspectType,ng) select blockNo || '_' || InspectType as BlockNo_InspectType, count(*) as 'ng' from spc where OPJudge='1' group by blockNo,InspectType""")
            # 以上数据是 新来的数据
            self.q.put("解析文件结束")

            db="./db/"+gv.sample_name+".db"
            c.execute("ATTACH DATABASE ? AS d", (db,))
            # c.executescript("""INSERT INTO d.sample (BlockNo_InspectType,ng) select blockNo || '_' || InspectType as BlockNo_InspectType, count(*) as 'ng' from spc where OPJudge='-1' group by blockNo,InspectType""")
            sql = 'select ds.BlockNo_InspectType,cs.ng,ds.ng from d.sample as ds join sample as cs on ds.BlockNo_InspectType = cs.BlockNo_InspectType where cs.ng < ds.ng'
            c.executescript(sql)  #  新数据sample as cs 和 标准数据（d.sampe）对比:ds.BlockNo_InspectType,cs.ng,ds.ng  where cs.ng < ds.ng
            
            self.q.put("附加标准数据结束")

            records = c.fetchall()
            if len(records)>0:
                # 有数据就说明 以下错误没有检查出来
                msg = f"compare_fail:{gv.sample_name}:{gv.totalErrors}\n"
                for record in records:
                    msg = msg + f"{record[0]}:{record[1]}:{record[2]}\n"
                self.q.put(msg)
                self.sendUdpMsg(msg)
                self.q.put("对比结束")
            else:
                c.execute("select errornum from d.programname")
                errornum = int(c.fetchone()[0])
                
                # 以下找到了实际比标准多个错误，知道了多几个
                sql = 'select ds.BlockNo_InspectType, sum(cs.ng-ds.ng) as ng from d.sample as ds join sample as cs on ds.BlockNo_InspectType = cs.BlockNo_InspectType '
                r = c.execute(sql)
                ngnum = int(c.fetchone()[1])
                if ngnum<=errornum:
                    # 可以接受
                    self.q.put("对比成功，实际与标准ng数差："+str(ngnum)+",标准ng数："+str(errornum))
                    msg = f"compare_ok:{gv.sample_name}:{ngnum},{errornum}"
                    self.sendUdpMsg(msg)
                else:
                    # 不可接受
                    self.q.put("对比失败，实际与标准ng数差："+str(ngnum)+",标准ng数："+str(errornum))
                    msg = f"compare_fail:{gv.sample_name}:{gv.totalErrors}"
                    self.sendUdpMsg(msg)
            c.close()
            
            log.info("样本处理结束")
        except Exception as ex:
            log.error("增加文件：" + spc_fullname + "失败，原因：" + str(ex))
            # todo 向 log程序发出通知（失败）
            msg = "compare_error:"+str(ex)
            self.sendUdpMsg(msg)
        finally:
            gv.sample_event.clear()
            
    
    def create_sample(self,spc_fullname):
        log =  AppLogger('my_app.log', max_bytes=1024*20, backup_count=2)
        try:
            spc_fullname = os.path.normpath(spc_fullname)
            config = ConfigManager()
            paths = config.paths()
            self.q.put("clear")
            self.q.put("开始处理样本："+spc_fullname)
            
            # 解析文件
            file_info,c = parse_scp(spc_fullname,cpfile=False)
            # 统计结果写入sample.db 中 sample 表
            db = self.empty_db(file_info,gv.totalErrors)
            c.execute("ATTACH DATABASE ? AS d", (db,))
            c.executescript("""INSERT INTO d.sample (BlockNo_InspectType,ng) select blockNo || '_' || InspectType as BlockNo_InspectType, count(*) as 'ng' from spc where OPJudge='1' group by blockNo,InspectType""")
            c.close()
            # 使用 udp 协议 向log日志程序 的 9090 端口 发出通知 
            programeName = gv.sanitize_name(file_info['ProgramName']+"_"+file_info['BoardID'])
            msg = f"sample_ok:{programeName}:{gv.totalErrors}"
            self.sendUdpMsg(msg)
            log.info("样本处理结束")
        except Exception as ex:
            log.error("增加文件：" + spc_fullname + "失败，原因：" + str(ex))
            msg = "sample_failed:"+spc_fullname
            self.sendUdpMsg(msg)
        finally:
            gv.sample_event.clear()
            
        return
    def on_created(self, event):
        log =  AppLogger('my_app.log', max_bytes=1024*5000, backup_count=2)
        log.info("增加文件："+event.src_path)
        if gv.compare_event.is_set():
            self.compare_sample(event.src_path)
            return
        elif gv.sample_event.is_set():
            self.create_sample(event.src_path)
            return
        
        try:
            spc_fullname = os.path.normpath(event.src_path)
            
            config = ConfigManager()
            paths = config.paths()
            self.q.put("clear")
            self.q.put("开始处理："+spc_fullname)
            file_info,c = parse_scp(spc_fullname)
            
            self.q.put("分析spc  csv文件完")
            
            config_manager = ConfigManager('config.ini')
            failFmt = config_manager.get_option('FMT','fail')
            # 文件名要用程序名 + 条码保存 + 20240430 20 49 13 457
            t = datetime.datetime.now().strftime("%Y%m%d%H%M%S%f")
            # filename = paths['dpath']+"/"+file_info['ProgramName']+"_"+file_info['BoardID']+"_"+t+".csv"
            filename = paths['dpath']+"/"+file_info['BoardID']+"_"+t+".txt"
            r = ngRows(failFmt,c)
            if isinstance(r,list):
                self.q.put("ng行数:%d"%len(r))
                with open(filename,'a',encoding='utf-8') as f:
                    for i in r:
                        f.write(",".join(i)+"\n")
                        
            log.info("ngRows:"+str(r))
            
            passFmt = config_manager.get_option('FMT','pass')
            r = okRows(passFmt,c)
            if isinstance(r,list):
                self.q.put("ok行数:%d"%len(r))
                with open(filename,'a',encoding='utf-8') as f:
                    for i in r:
                        f.write(",".join(i)+"\n")
            log.info("passFmt:"+str(r))
            
            # r = statistics(c)
            # log.info("statistics:"+str(r))
            
        except Exception as ex:
            self.q.put("处理文件出错："+spc_fullname+","+str(ex))
            log.error("处理文件出错："+spc_fullname+","+str(ex))
            
    def sendUdpMsg(self,msg):
        sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        r = sock.sendto(msg.encode("utf-8"), (gv.logServerIp, 9090))
        self.q.put(f"发送网络消息{r}：{msg}")
        sock.close()
            
def watcher(path,q):
    event_handler = MyHandler(q)
    observer = Observer()
    log =  AppLogger('my_app.log')
    log.info("开始监控路径："+path)
    observer.schedule(event_handler, path=path, recursive=True)
    observer.start()
    try:
        while True:
            observer.join(1)
    except KeyboardInterrupt:
        observer.stop()